<?php
/**
 * dbfront
 * (c) 2010-2013 Stephen Adkins <spadkins@gmail.com>
 * This software began development in 2010.
 * It is based on code dating from 1999.
 *
 * License: GPL 2.0
 * The contents of this file may be used under the terms of
 * the GNU General Public License Version 2 or later (the "GPL").
 *
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 * */

/**
 DSN Styles

    PDO DSN Format: driver:param1=value1;param2=value2

    sqlite:
        sqlite:/opt/databases/mydb.sq3
        sqlite::memory:

    mysql:
        host        - The hostname on which the database server resides.  
        port        - The port number where the database server is listening.  
        dbname      - The name of the database.  
        unix_socket - The MySQL Unix socket (shouldn't be used with host or port).  
        charset     - The character set. See the character set concepts documentation for more information.
        $dbh = new PDO(
            'mysql:host=hostname;dbname=ssldb',
            'username',
            'password',
            array(
                PDO::MYSQL_ATTR_LOCAL_INFILE => true,                # Enable LOAD LOCAL INFILE.
                PDO::MYSQL_ATTR_INIT_COMMAND => 'set names utf8',    # Command to execute when connecting to the MySQL server. Will automatically be re-executed when reconnecting.
                PDO::MYSQL_ATTR_FOUND_ROWS   => true,                # Return the number of found (matched) rows, not the number of changed rows.
            )
        );
        $dbh = new PDO('mysql:host=xxx;port=xxx;dbname=xxx', 'xxx', 'xxx', array( PDO::ATTR_PERSISTENT => false));
    
    pgsql:
        host     - The hostname on which the database server resides.  
        port     - The port on which the database server is running.  
        dbname   - The name of the database.  
        user     - The name of the user for the connection. If you specify the user name in the DSN, PDO ignores the value of the user name argument in the PDO constructor.  
        password - The password of the user for the connection. If you specify the password in the DSN, PDO ignores the value of the password argument in the PDO constructor.  

    oci:
        dbname (Oracle Instant Client) - The URI for the Oracle Instant Client connection takes the form of dbname=//hostname:port-number/database.
                                         If you are connecting to a database defined in tnsnames.ora, use only the name of the database: dbname=database.
        charset                        - The client-side character set for the current environment handle.
        // Connect to a database defined in tnsnames.ora
        oci:dbname=mydb
        // Connect using the Oracle Instant Client
        oci:dbname=//localhost:1521/mydb
        $dbc = new PDO('oci:dbname=192.168.10.145/orcl;charset=CL8MSWIN1251', 'username', 'password');
 * */

namespace App;
abstract class PDODatabase extends SQLDatabase {
    protected $dbh;

    protected function _connect () {
        global $trace;
        if ($trace) trace_entry();
        $this->_preconnect();
        $dsn    = $this->_dsn();
        $dbattr = $this->_dbattr();
        if ($this->persistent) {
            $dbattr[\PDO::ATTR_PERSISTENT] = true;
        }

        #echo "_connect() : $this->dbuser/xxxxx@$dsn\n";
        if (isset($this->db_connection_id)) {
            $db_connection_id = $this->db_connection_id;
            if (isset(self::$db_connection_cache[$db_connection_id])) {
                $this->dbh = self::$db_connection_cache[$db_connection_id];
                debug_print("CACHING ($this->db_name: {$this->dbuser}@$this->db_connection_name): cache hit. reusing connection. (db_connection_id=[$db_connection_id])");
            }
            else {
                $this->dbh = new \PDO($dsn, $this->dbuser, $this->dbpass, $dbattr);
                self::$db_connection_cache[$db_connection_id] = $this->dbh;
                debug_print("CACHING ($this->db_name: {$this->dbuser}@$this->db_connection_name): cache miss. new connection.    (db_connection_id=[$db_connection_id])");
            }
        }
        else {
            $this->dbh = new \PDO($dsn, $this->dbuser, $this->dbpass, $dbattr);
            debug_print("CACHING ($this->db_name: {$this->dbuser}@$this->db_connection_name): no attempt to use connection cache (no db_connection_id)");
        }
        #echo "_connect() : connected [$this->dbh]\n";
        if (!$this->dbh) throw new Exception('Could not connect to database.');
        $this->dbh->setAttribute(\PDO::ATTR_CASE, \PDO::CASE_LOWER);             # force column names to lower case
        $this->dbh->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);   # throw exception on database error
        $this->_postconnect();
        if ($trace) trace_exit();
    }
    protected function _preconnect () {}
    protected function _postconnect () {}

    protected function _disconnect () {
        global $trace;
        if ($trace) trace_entry();
        if (isset($this->dbh)) {
            $this->dbh = null;
        }
        if ($trace) trace_exit();
    }

    protected function _dsn () {
        global $trace;
        if ($trace) trace_entry();
        if (isset($this->dsn)) {
            $dsn = $this->dsn;
        }
        if (!isset($dsn)) {
            throw new \Exception("Could not determine the appropriate DSN for the requested database ($this->name).");
        }
        if ($trace) trace_exit($dsn);
        return($dsn);
    }

    protected function _dbattr () {
        global $trace;
        if ($trace) trace_entry();
        $dbattr = Array();
        if ($trace) trace_exit($dbattr);
        return($dbattr);
    }

    protected function _get_select_sth ($table, $params = null, $columns = null, &$options = null, &$bind_values = null) {
        global $trace, $debug_sql, $debug_fh;
        if ($trace) trace_entry();

        if (!$options) $options = array();

        $sql  = $this->_get_select_sql($table, $params, $columns, $options, $bind_values);
        if ($debug_sql) {
            fwrite($debug_fh, '<!-- DEBUG_SQL:');
            if (isset($bind_values) && count($bind_values) > 0) {
                fwrite($debug_fh, ' bind values => (');
                foreach ($bind_values as $i => $value) {
                    if ($i > 0) fwrite($debug_fh, ', ');
                    fwrite($debug_fh, $value);
                }
                fwrite($debug_fh, ')');
            }
            fwrite($debug_fh, "\n" . $sql . " -->\n");
        }
        $sth = $this->dbh->prepare($sql);
        if (!$sth) {
            $errorinfo = $this->dbh->errorInfo();
            # Element Information
            # 0   SQLSTATE error code (a five characters alphanumeric identifier defined in the ANSI SQL standard).
            # 1   Driver-specific error code.
            # 2   Driver-specific error message.
            $message = 'Error in SQL';
            #$message = 'Error in SQL: [ANSI Code:' . $errorinfo[0] . ', Native Code:' . $errorinfo[1] . '] ' . errorinfo[2];  # TODO
            throw new \Exception($message . ' : ' . $sql);
        }
        else {
            if (isset($bind_values) && count($bind_values) > 0) $sth->execute($bind_values);
            else                                                $sth->execute();

            if (isset($options) && isset($options['query_def'])) {
                if (! is_array($options['query_def'])) $options['query_def'] =  array();
                $query_def            =& $options['query_def'];
                $query_def['column']  =  array();
                $column_defs          =& $query_def['column'];
                $query_def['columns'] =  array();
                $columns              =& $query_def['columns'];
                $num_fields = $sth->columnCount();
                for ($i = 0; $i < $num_fields; $i++) {
                    $meta = $sth->getColumnMeta($i);
                    if (!$meta) {
                        throw new \Exception("ERROR: fetching metadata for column {$i} : " . $sql);
                    }
                    $column_name = strtolower($meta['name']);
                    $column_defs[$column_name] = array('column_name'  => $column_name,
                                                       'column_label' => $this->make_label($column_name),
                                                       'column_type'  => $this->native_type_to_app_type($meta['native_type']));
                    $columns[] = $column_name;
                }
            }
        }
        if ($trace) trace_exit($sth);
        return($sth);
    }

    public function get_rows ($table, $params = null, $columns = null, &$options = null) {
        global $trace, $debug_sql, $debug_fh;
        if ($trace) trace_entry();
        $start_time = microtime(true);
        $bind_values = array();
        $sth  = $this->_get_select_sth($table, $params, $columns, $options, $bind_values);
        $rows = $sth->fetchAll(\PDO::FETCH_NUM);
        $run_db_time = microtime(true)-$start_time;
        if ($debug_sql >= 2) {
            foreach ($rows as $row) {
                fwrite($debug_fh, '<!-- DEBUG_SQL: [' . implode(',', $row) . "]\n");
            }
        }
        if ($debug_sql) fwrite($debug_fh, '<!-- DEBUG_SQL: rows=[' . count($rows) . '] time=[' . round($run_db_time,6) . "] -->\n");
        $this->context->accumulateDatabaseActivity($run_db_time, 1, count($rows), 0);
        if ($trace) trace_exit(count($rows).' rows');
        return($rows);
    }

    public function get_hashes ($table, $params = null, $columns = null, &$options = null) {
        global $trace, $debug_sql, $debug_fh;
        if ($trace) trace_entry();
        $start_time = microtime(true);
        $bind_values = array();
        $sth  = $this->_get_select_sth($table, $params, $columns, $options, $bind_values);
        $rows = $sth->fetchAll(\PDO::FETCH_ASSOC);
        $run_db_time = microtime(true)-$start_time;
        if ($debug_sql >= 2) {
            foreach ($rows as $row) {
                fwrite($debug_fh, '<!-- DEBUG_SQL: [');
                $first = 1;
                foreach ($row as $key => $value) {
                    if (!$first) fwrite($debug_fh, ',');
                    fwrite($debug_fh, "$key=>$value");
                    $first = 0;
                }
                fwrite($debug_fh, "]\n");
            }
        }
        if ($debug_sql) fwrite($debug_fh, '<!-- DEBUG_SQL: rows=[' . count($rows) . '] time=[' . round($run_db_time,6) . "] -->\n");
        $this->context->accumulateDatabaseActivity($run_db_time, 1, count($rows), 0);
        if ($trace) trace_exit(count($rows).' rows');
        return($rows);
    }

    public function get_column ($table, $params = null, $column = null, &$options = null) {
        global $trace, $debug_sql, $debug_fh;
        if ($trace) trace_entry();
        $start_time = microtime(true);
        if (isset($column)) $columns = array($column);
        else                $columns = null;
        $bind_values = array();
        $sth  = $this->_get_select_sth($table, $params, $columns, $options, $bind_values);
        $column_data = $sth->fetchAll(\PDO::FETCH_COLUMN);
        $run_db_time = microtime(true)-$start_time;
        if ($debug_sql >= 2) fwrite($debug_fh, '<!-- DEBUG_SQL: [' . implode(', ', $column_data) . "]\n");
        if ($debug_sql) fwrite($debug_fh, '<!-- DEBUG_SQL: rows=[' . count($column_data) . '] time=[' . round($run_db_time,6) . "] -->\n");
        $this->context->accumulateDatabaseActivity($run_db_time, 1, count($column_data), 0);
        if ($trace) trace_exit($column_data);
        return($column_data);
    }

    public function execute ($sql, $bind_values = null) {
        global $trace, $debug_sql, $debug_fh;
        if ($trace) trace_entry();
        if (preg_match('/\\{.*\\}/',$sql)) $sql = $this->substitute_sql_vars($sql);
        if ($debug_sql) {
            fwrite($debug_fh, '<!-- DEBUG_SQL:');
            if (isset($bind_values) && count($bind_values) > 0) {
                fwrite($debug_fh, ' bind values => (');
                foreach ($bind_values as $i => $value) {
                    if ($i > 0) fwrite($debug_fh, ', ');
                    fwrite($debug_fh, $value);
                }
                fwrite($debug_fh, ')');
            }
            fwrite($debug_fh, "\n" . $sql . " -->\n");
        }
        $start_time = microtime(true);
        try {
            $sth = $this->dbh->prepare($sql);
            if (isset($bind_values) && count($bind_values) > 0) $success = $sth->execute($bind_values);
            else                                                $success = $sth->execute();
            $nrows = $success ? $sth->rowCount() : 0;
            #echo "SQLDatabase.execute()=$nrows\n";
            $run_db_time = microtime(true)-$start_time;
            $error_code = $this->dbh->errorCode();
            if ($debug_sql) fwrite($debug_fh, '<!-- DEBUG_SQL: rows=[' . $nrows . '] time=[' . round($run_db_time,6) . "] -->\n");
            $this->context->accumulateDatabaseActivity($run_db_time, 1, 0, $nrows);
        }
        catch (Exception $e) {
            throw new \Exception($e->getMessage() . ' : ' . $sql);
        }
        if ($error_code && $error_code != '00000') {
            $error_info        = $this->dbh->errorInfo();
            $driver_error_code = $error_info[1];
            $error_msg         = $error_info[2];
            throw new \Exception("ERROR: Database($this->name).execute(): [PDO:$error_code,Driver:$driver_error_code] : $error_msg : $sql");
        }
        if ($trace) trace_exit($nrows);
        return($nrows);
    }

    public function _quote ($str_value) {
        $str_value = $this->dbh->quote($str_value);
        return($str_value);
    }

    public function last_insert_id ($table) {
        global $trace;
        if ($trace) trace_entry();
        $id = $this->dbh->lastInsertId();
        if ($trace) trace_exit($id);
        return($id);
    }

    public function begin_work () {
        global $trace;
        if ($trace) trace_entry();
        $this->dbh->beginTransaction();
        if ($trace) trace_exit();
    }

    public function commit () {
        global $trace;
        if ($trace) trace_entry();
        $this->dbh->commit();
        if ($trace) trace_exit();
    }

    public function rollback () {
        global $trace;
        if ($trace) trace_entry();
        $this->dbh->rollback();
        if ($trace) trace_exit();
    }

    public function export_table ($table_name, $filename = null, $options = null) {
        global $trace, $debug_sql, $debug_fh;
        if ($trace) trace_entry();
        $params  = (isset($options) && isset($options['params'])) ? $options['params'] : null;
        $columns = (isset($options) && isset($options['columns'])) ? $options['columns'] : null;
        $start_time = microtime(true);
        # OPEN FILE
        $fh = fopen($filename, "w");
        $bind_values = array();
        $sth   = $this->_get_select_sth($table_name, $params, $columns, $options, $bind_values);
        $ncols = $sth->columnCount();
        $row   = $sth->fetch(\PDO::FETCH_NUM);
        $text_column   = array();
        $binary_column = array();

        for ($c = 0; $c < $ncols; $c++) {
            $meta = $sth->getColumnMeta($c);
            if (!$meta) { throw new \Exception("ERROR: fetching metadata for column {$i}"); }
            $column_name = strtolower($meta['name']);
            $type = $this->native_type_to_app_type($meta['native_type']);
            $text_column[$c]   = ($type === 'string' || $type === 'text') ? 1 : 0;
            $binary_column[$c] = ($type === 'blob') ? 1 : 0;
            if ($c > 0) fwrite($fh,',');
            fwrite($fh, $column_name);
        }
        fwrite($fh,"\n");

        $nrows = 0;
        $run_db_time = microtime(true)-$start_time;
        while ($row) {
            $nrows++;
            #fwrite($fh, implode(',', $row) . "\n");
            for ($c = 0; $c < $ncols; $c++) {
                if ($c > 0) fwrite($fh,',');
                if ($binary_column[$c]) {
                    fwrite($fh, '[unprintable]');
                }
                elseif ($text_column[$c]) {
                    if (strpbrk($row[$c],",\\\"\n")) {
                        fwrite($fh, '"' . str_replace(array("\\","\"","\n"), array("\\\\","\\\"","\\n"), $row[$c]) . '"');
                    }
                    else {
                        fwrite($fh,$row[$c]);
                    }
                }
                else {
                    fwrite($fh,$row[$c]);
                }
            }
            fwrite($fh,"\n");
            if ($debug_sql >= 2) {
                fwrite($debug_fh, '<!-- DEBUG_SQL: [' . implode(',', $row) . "]\n");
            }
            $row  = $sth->fetch(\PDO::FETCH_NUM);
        }
        if ($debug_sql) fwrite($debug_fh, '<!-- DEBUG_SQL: rows=[' . $nrows . '] time=[' . round($run_db_time,6) . "] -->\n");
        $this->context->accumulateDatabaseActivity($run_db_time, 1, $nrows, 0);
        fclose($fh);
        if ($trace) trace_exit($nrows);
        return($nrows);
    }
}

